/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package server;
//<editor-fold defaultstate="collapsed" desc=" các thư viện sử dụng">
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.xpath.*;
import org.w3c.dom.*;
import org.xml.sax.SAXException;


//</editor-fold>
/**
 *
 * @author KingofSpeed
 */
public class DataTable 
{
       //<editor-fold defaultstate="collapsed" desc=" properties ">
    private int nColumn;
    private DefaultTableModel model;
    private List<Column> columns;
    private int nRows;
    MainWindow parent;
    private String name;

     //</editor-fold>
       //<editor-fold defaultstate="collapsed" desc=" hàm Khởi tạo">
    public DataTable( MainWindow _main )
    {
        nRows = nColumn = 0;
        model = new DefaultTableModel();
        columns = new ArrayList<Column>();
        parent = _main;
        name = "";
    }
     //</editor-fold>
       //<editor-fold defaultstate="collapsed" desc=" load dữ liêu từ file xml">
    public boolean LoadData( String tableName)
    {
        nRows = nColumn = 0;
        columns = new ArrayList<Column>();
        DocumentBuilderFactory fac = DocumentBuilderFactory.newInstance();
        fac.setNamespaceAware(true);
        DocumentBuilder documentBuilder = null;
        Document doc;
        try 
        {
            documentBuilder = fac.newDocumentBuilder();
            try 
            {
               
                doc = documentBuilder.parse( new File( tableName ));
                doc.getDocumentElement().normalize();
                NodeList listColumn = doc.getElementsByTagName("Column");
                nColumn = listColumn.getLength();
                if ( nColumn > 0 )
                    name = listColumn.item(0).getParentNode().getAttributes().getNamedItem("name").getNodeValue();
                ArrayList col = new ArrayList();
                for ( int i = 0; i < nColumn; i++ )
                {
                   
                    Column c = new Column();
                    c.setType(listColumn.item(i).getAttributes().getNamedItem("type").getNodeValue());
                    c.setTitle(listColumn.item(i).getAttributes().getNamedItem("title").getNodeValue());
                   
                    if ( listColumn.item(i).getAttributes().getNamedItem("isNotNull").getNodeValue().compareTo("Yes") == 0)
                    {
                        c.setIsNotNull(true);
                    }
                    if ( listColumn.item(i).getAttributes().getNamedItem("isPrimaryKey").getNodeValue().compareTo("Yes") == 0)
                    {
                        c.setIsPrimary(true);
                    }
                    
                    GetForeignKey(c, listColumn.item(i).getAttributes().getNamedItem("foreignKey").getNodeValue() );
                    
                    XPath xpath = XPathFactory.newInstance().newXPath();
                    try 
                    {
                        String role = "//Table//Column[@title='" + String.valueOf(c.getTitle()) +  "']//row";
                        XPathExpression xpex = xpath.compile(role);
                        Object result = xpex.evaluate( doc , XPathConstants.NODESET);
                        NodeList rows = (NodeList)result;
                        c.setnRow(rows.getLength());
                        for ( int j = 0; j < rows.getLength(); j++ )
                        {
                            c.getListRow().add(rows.item(j).getAttributes().getNamedItem("value").getNodeValue());
                        }
                    } catch (XPathExpressionException ex) { }
                    columns.add(c);
                    if ( c.isIsNotNull() == true )
                        col.add(c.getTitle() + " (not null)");
                    else
                        col.add(c.getTitle());
                }
                 
                model = new DefaultTableModel(null, col.toArray());
                CountRows();
                ReadData();
                
                parent.ChangeIcon();
            } 
            catch (SAXException ex)           {
            } catch (IOException ex) {            }
        } 
        catch (ParserConfigurationException ex)
        {
        }
        return true;
    }
     //</editor-fold>
       //<editor-fold defaultstate="collapsed" desc=" Lưu tên thay đổi xuống file xml">
    public void SaveTableName( String oldName )
    {
        try
        {
            new File( parent.linkDatabase + "\\" + oldName + ".xml").renameTo(new File( parent.linkDatabase + "\\" + name + ".xml"));
            DocumentBuilderFactory fac = DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = fac.newDocumentBuilder();
            Document doc = builder.parse(parent.linkDatabase + "\\" + name + ".xml");
           
            Node table = doc.getElementsByTagName("Table").item(0);
            Node newName = table.getAttributes().getNamedItem("name");
            newName.setTextContent(name);
            
            ToolSQL toolSQL = new ToolSQL( parent );
            toolSQL.RenameTable(oldName, name);
            
            TransformerFactory trans = TransformerFactory.newInstance();
            Transformer transform = trans.newTransformer();
            DOMSource dom = new DOMSource(doc);
            StreamResult result = new StreamResult( new File(parent.linkDatabase + "\\" + name + ".xml"));
            transform.transform(dom, result);
        }
        catch( Exception ex ){}
    }
     //</editor-fold>
       //<editor-fold defaultstate="collapsed" desc=" Lưu foreign key vào file xml">
    public void SaveForeignKeyChange( int index)
    {
        try
        {
            DocumentBuilderFactory fac = DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = fac.newDocumentBuilder();
            Document doc = builder.parse(parent.linkDatabase + "\\" + name + ".xml");
        
            XPath xpath = XPathFactory.newInstance().newXPath();
                try
                {
                    String role = "//Table//Column[@title ='"+ columns.get(index).getTitle() +"']";
                    XPathExpression xpex = xpath.compile(role);
                    Object result = xpex.evaluate(doc, XPathConstants.NODESET);
                    Node node = ((NodeList)result).item(0);
                    Node cur = node.getAttributes().getNamedItem("foreignKey");
                    if ( columns.get(index).isIsHaveForeignKey() )
                    {
                        String fkey = "";
                        for ( int j = 0; j< columns.get(index).getRef().size(); j++)
                        {
                            fkey += columns.get(index).getRef().get(j) + ";";       
                        }
                        cur.setTextContent(fkey);  
                    }
                    else
                        cur.setTextContent("");
                 
                }
                catch(Exception ex ){}
            
            TransformerFactory trans = TransformerFactory.newInstance();
            Transformer transform = trans.newTransformer();
            DOMSource dom = new DOMSource(doc);
            StreamResult result = new StreamResult( new File(parent.linkDatabase + "\\" + name + ".xml"));
            transform.transform(dom, result);
        }
        catch( Exception ex ){}
    }
     //</editor-fold>
       //<editor-fold defaultstate="collapsed" desc=" lưu các thay đổi của cột như khóa chính, is not null, type">
    public void SaveColumnsChange()
    {
        try
        {
            DocumentBuilderFactory fac = DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = fac.newDocumentBuilder();
            Document doc = builder.parse(parent.linkDatabase + "\\" + name + ".xml");
        
            XPath xpath = XPathFactory.newInstance().newXPath();
            for ( int i = 0; i < columns.size(); i++ )
            {
                try
                {
                    String role = "//Table//Column[@title ='"+ columns.get(i).getTitle() +"']";
                    XPathExpression xpex = xpath.compile(role);
                    Object result = xpex.evaluate(doc, XPathConstants.NODESET);
                    NodeList list = ((NodeList)result);
                    Node node = null;
                        node = list.item(0);
                    Node cur;
                    cur = node.getAttributes().getNamedItem("isPrimaryKey");
                    if ( columns.get(i).isIsPrimary() )
                        cur.setTextContent("Yes");
                    else
                        cur.setTextContent("No");
                    cur = node.getAttributes().getNamedItem("isNotNull");
                    if ( columns.get(i).isIsNotNull() )
                        cur.setTextContent("Yes");
                    else
                        cur.setTextContent("No");
                    cur = node.getAttributes().getNamedItem("type");
                    cur.setTextContent(columns.get(i).getType());        
                }
                catch(Exception ex ){}
            }
            
            TransformerFactory trans = TransformerFactory.newInstance();
            Transformer transform = trans.newTransformer();
            DOMSource dom = new DOMSource(doc);
            StreamResult result = new StreamResult( new File(parent.linkDatabase + "\\" + name + ".xml"));
            transform.transform(dom, result);
        }
        catch( Exception ex ){}
    }
     //</editor-fold>
       //<editor-fold defaultstate="collapsed" desc=" lưu lại tên mới cho các column dc đổi tên">
    public void SaveColumnsRenamed( List<String> columnsToRename)
    {
        try
        {
            DocumentBuilderFactory fac = DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = fac.newDocumentBuilder();
            Document doc = builder.parse(parent.linkDatabase + "\\" + name + ".xml");
            ArrayList<String> oldName = new ArrayList<String>();
            ArrayList<String> newName = new ArrayList<String>();
            ToolSQL toolSQL = new ToolSQL(parent);
            for ( int i = 0 ; i < columnsToRename.size(); i++ )
            {
                oldName.add(toolSQL.GetNameFromForeignKey( columnsToRename.get(i), true));
                newName.add(toolSQL.GetNameFromForeignKey( columnsToRename.get(i), false));
            }
            XPath xpath = XPathFactory.newInstance().newXPath();
            for ( int i = 0; i < columnsToRename.size(); i++ )
            {
                try
                {
                    String role = "//Table//Column[@title ='"+ oldName.get(i) +"']";
                   
                    XPathExpression xpex = xpath.compile(role);
                    Object result = xpex.evaluate(doc, XPathConstants.NODESET);
                    Node node = ((NodeList)result).item(0);
                    Node cur = node.getAttributes().getNamedItem("title");
                    cur.setTextContent(newName.get(i));
                }
                catch(Exception ex ){}
            }
            
            TransformerFactory trans = TransformerFactory.newInstance();
            Transformer transform = trans.newTransformer();
            DOMSource dom = new DOMSource(doc);
            StreamResult result = new StreamResult( new File(parent.linkDatabase + "\\" + name + ".xml"));
            transform.transform(dom, result);
        }
        catch( Exception ex ){}
    }
     //</editor-fold>
       //<editor-fold defaultstate="collapsed" desc=" xóa các cột đã được người dùng delete ra khỏi file xml">
    public void SaveColumnsDeleted( List<String> columnsToDelete)
    {
        try
        {
            DocumentBuilderFactory fac = DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = fac.newDocumentBuilder();
            Document doc = builder.parse(parent.linkDatabase + "\\" + name + ".xml");
            XPath xpath = XPathFactory.newInstance().newXPath();
            for ( int i = 0; i < columnsToDelete.size(); i++ )
            {
                try
                {
                    String role = "//Table//Column[@title ='"+ columnsToDelete.get(i) +"']";
                    XPathExpression xpex = xpath.compile(role);
                    Object result = xpex.evaluate(doc, XPathConstants.NODESET);
                    Node node = ((NodeList)result).item(0);
                    node.getParentNode().removeChild(node);
                }
                catch(Exception ex ){}
            }
            
            TransformerFactory trans = TransformerFactory.newInstance();
            Transformer transform = trans.newTransformer();
            DOMSource dom = new DOMSource(doc);
            StreamResult result = new StreamResult( new File(parent.linkDatabase + "\\" + name + ".xml"));
            transform.transform(dom, result);
        }
        catch( Exception ex ){}
    }
     //</editor-fold>

       //<editor-fold defaultstate="collapsed" desc=" đổi các giá trị trong model lưu xuống theo các column">
    public boolean SaveModelToRow()
    {
        for ( int i = 0; i < columns.size(); i++)
        {
            columns.get(i).getListRow().clear();
            for ( int j = 0; j < model.getRowCount(); j++)
            {
                try
                {
                    if ( i >= model.getColumnCount())
                    {
                        columns.get(i).getListRow().add("");
                    }
                    else
                    {
                        String value = String.valueOf(model.getValueAt(j, i));
                        if ( value == null || (!model.getColumnName(i).equals(columns.get(i).getTitle())
                                && !model.getColumnName(i).equals(columns.get(i).getTitle() + " (not null)")))
                        {
                            columns.get(i).getListRow().add("");
                        }
                        else
                            columns.get(i).getListRow().add(value);
                    }
                   
                }
                catch ( Exception ex ){}
            }
        }
        return true;
    }
     //</editor-fold>
       //<editor-fold defaultstate="collapsed" desc=" lưu dữ liệu xuống file xml">
    public boolean SaveData()
    {
        try
        {
            SaveModelToRow();
            DocumentBuilderFactory fac = DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = fac.newDocumentBuilder();
            Document doc = builder.newDocument();
            
            Element root = doc.createElement("Table");
            root.setAttribute("name", name);
            doc.appendChild(root);
            
            for ( int i = 0;i < columns.size(); i++)
            {
                Element column = doc.createElement("Column");
                column.setAttribute("title", columns.get(i).getTitle());
                if ( columns.get(i).isIsNotNull() == true )
                    column.setAttribute("isNotNull", "Yes");
                else 
                    column.setAttribute("isNotNull", "No");
                if ( columns.get(i).isIsPrimary() == true )
                    column.setAttribute("isPrimaryKey", "Yes");
                else
                    column.setAttribute("isPrimaryKey", "No");
                if ( columns.get(i).isIsHaveForeignKey() ==  true )
                {
                    String fkey = "";
                    for ( int j = 0; j< columns.get(i).getRef().size(); j++)
                    {
                        fkey += columns.get(i).getRef().get(j) + ";";
                    }
                    column.setAttribute("foreignKey", fkey);
                }     
                else
                    column.setAttribute("foreignKey", "");
                column.setAttribute("type", columns.get(i).getType());
                for ( int j = 0; j < columns.get(i).getnRow(); j++)
                {
                    String x =  columns.get(i).getListRow().get(j);
                    if ( x.contains("&"))
                        x.replace("&", "&amp;");
                    if ( x.contains("<"))
                        x.replace("<", "&lt;");
                    if ( x.contains("\""))
                        x.replace("\"", "&quot;");
                    if ( x.contains("\'"))
                        x.replace("\'", "&#39;");
                    Element row = doc.createElement("row");
                    row.setAttribute("value", columns.get(i).getListRow().get(j));
                    column.appendChild(row);
                }
                root.appendChild(column);            
            }
            TransformerFactory transfac = TransformerFactory.newInstance();
            Transformer trans = transfac.newTransformer();
            trans.setOutputProperty( OutputKeys.OMIT_XML_DECLARATION, "yes");
            trans.setOutputProperty( OutputKeys.INDENT, "yes");
            
            File file = new File( parent.linkDatabase + "\\" + name + ".xml");
            StreamResult result = new StreamResult(file);
            DOMSource source  = new DOMSource(doc);
            trans.transform(source, result );
            return true;
        }
        catch ( Exception ex ){ return false ;}
    }
     //</editor-fold>
       //<editor-fold defaultstate="collapsed" desc=" đếm số dòng dữ liệu">
    private void CountRows()
    {
        if ( columns.size() == 0 )
            return ;
        nRows = columns.get(0).getnRow();
        for ( int i = 0; i < columns.size(); i++)
        {
            if ( nRows <  columns.get(i).getnRow())
                nRows = columns.get(i).getnRow();
        }
    }
     //</editor-fold>
       //<editor-fold defaultstate="collapsed" desc=" đọc dữ liệu từ cột lưu sang model">
    private void ReadData()
    {
        for ( int i = 0; i < nRows; i++)
        {
            ArrayList data = new ArrayList();
            for ( int j = 0; j < columns.size(); j++)
            {
                try{
                    if ( columns.get(j).getListRow().size() < i)
                        columns.get(j).getListRow().add("");
                    data.add(columns.get(j).getListRow().get(i));
                }
                catch( Exception ex ){}
            }
            model.addRow(data.toArray());
        }
    }
     //</editor-fold>
       //<editor-fold defaultstate="collapsed" desc=" load lên các khóa ngoại">
    private void GetForeignKey( Column c, String key) {
       
        try
        {
            if ( key.length() == 0 )
            {
                c.setIsHaveForeignKey(false);
                return;
            }
            String fkey[];
            fkey = key.split(";");
            for ( int i= 0; i < fkey.length; i++)
            {
                c.getRef().add(fkey[i]);
            }
            if ( c.getRef().size() >  0)
                c.setIsHaveForeignKey(true);
            else
                c.setIsHaveForeignKey(false);     
        }
        catch ( Exception ex ){}
        
    }
     //</editor-fold>
       //<editor-fold defaultstate="collapsed" desc=" kiểm tra vi phạm khóa chính">
    public boolean CheckPrimaryKey( int iRow, int iCol )
    {
        if ( !columns.get(iCol).isIsPrimary())
            return true;
        String value = String.valueOf(model.getValueAt(iRow, iCol));
        for ( int i = 0; i < model.getRowCount(); i++ )
        {
            if ( i != iRow )
            {
                if ( String.valueOf(model.getValueAt(i, iCol)).equals(value))
                {
                    return false;
                }
            }
        }
        return true;
    }
     //</editor-fold>
       //<editor-fold defaultstate="collapsed" desc=" kiểm tra xem có được để trống dòng này không">
    public boolean CheckNotNull( int iRow, int iCol )
    {
        if ( !columns.get(iCol).isIsNotNull())
            return true;
        String value = String.valueOf(model.getValueAt(iRow, iCol));
        if ( value.length() <= 0 )
        {
            
            return false;
        }
        return true;
    }
     //</editor-fold>
    
       //<editor-fold defaultstate="collapsed" desc=" Copy default table models sang một object khác (clone)">
        public DefaultTableModel Clone( )
        {
           try
           {
                ArrayList col = new ArrayList();
                for ( int i = 0; i < this.model.getColumnCount(); i++ )
                {
                    String colName = model.getColumnName(i);
                    String tam = " (not null)";
                    if ( colName.endsWith(tam))
                    {
                        colName = colName.substring(0,colName.length() - tam.length());
                    }
                    col.add( name + "." + colName);
                }
                DefaultTableModel newModel= new DefaultTableModel(null ,col.toArray());
                
                for ( int i = 0; i < this.model.getRowCount(); i++ )
                {
                    ArrayList row = new ArrayList();
                    for ( int j = 0; j < this.model.getColumnCount(); j++ )
                    {
                        Object a = model.getValueAt(i, j);
                        row.add(a);
                    }
                    newModel.addRow(row.toArray());
                }
                return newModel;
           }
           catch( Exception ex )
           {
               parent.ShowMessageBox(ex.toString(), name);
            return new DefaultTableModel();
           }
        }
        //</editor-fold>
       //<editor-fold defaultstate="collapsed" desc=" setter getter">
    public DefaultTableModel getModel() {
        return model;
    }

    public void setModel(DefaultTableModel model) {
        this.model = model;
    }

    public int getnColumn() {
        return columns.size();
    }

    public void setnColumn(int nColumn) {
        this.nColumn = nColumn;
    }
    
    public List<Column> getListColumn() {
        return columns;
    }

    public void setListColumn(List<Column> listColumn) {
        this.columns = listColumn;
    }
    public List<Column> getColumns() {
        return columns;
    }

    public void setColumns(List<Column> columns) {
        this.columns = columns;
    }

    public int getnRows() {
        return nRows;
    }

    public void setnRows(int nRows) {
        this.nRows = nRows;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public MainWindow getParent() {
        return parent;
    }

    public void setParent(MainWindow parent) {
        this.parent = parent;
    }
     //</editor-fold>
}
